﻿using System.Text;

namespace Dapper.Sharding
{
    internal class PostgreQuery : IQuery
    {
        public PostgreQuery(IDatabase db) : base(db)
        {

        }

        internal override void Init<T>(ITable<T> table, bool setField = true, bool setOrderby = true)
        {
            sqlTable.Append(table.Name);

            if (setField)
            {
                returnFields = table.SqlField.AllFields;
            }

            if (setOrderby)
            {
                sqlOrderBy = " ORDER BY " + table.SqlField.PrimaryKey;
            }
        }

        internal override void Init<T>(ITable<T> table, string asName, bool setField = true, bool setOrderby = true)
        {
            sqlTable.Append(table.Name);
            sqlTable.Append(" AS ");
            sqlTable.Append(asName);

            if (setField)
            {
                returnFields = asName + ".*";
            }

            if (setOrderby)
            {
                sqlOrderBy = " ORDER BY " + asName + "." + table.SqlField.PrimaryKey;
            }
        }

        public override IQuery InnerJoin<T>(ITable<T> table, string asName, string on)
        {
            sqlTable.Append(" INNER JOIN ");
            sqlTable.Append(table.Name);
            sqlTable.Append(" AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin<T>(ITable<T> table, string asName, string on)
        {
            sqlTable.Append(" LEFT JOIN ");
            sqlTable.Append(table.Name);
            sqlTable.Append(" AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery InnerJoin(string sql, string asName, string on)
        {
            sqlTable.Append(" INNER JOIN (");
            sqlTable.Append(sql);
            sqlTable.Append(") AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin(string sql, string asName, string on)
        {
            sqlTable.Append(" LEFT JOIN (");
            sqlTable.Append(sql);
            sqlTable.Append(") AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery InnerJoin(string tableName, string on)
        {
            sqlTable.Append(" INNER JOIN ");
            sqlTable.Append(tableName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin(string tableName, string on)
        {
            sqlTable.Append(" LEFT JOIN ");
            sqlTable.Append(tableName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override string Sql()
        {
            if (string.IsNullOrEmpty(returnFields))
            {
                returnFields = "*";
            }

            if (take == 0)
            {
                return $"SELECT{distinct} {returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}{forUpdate}";
            }
            else
            {
                return $"SELECT{distinct} {returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy} LIMIT {take} OFFSET {skip}{forUpdate}";
            }
        }

        public override string SqlCount()
        {
            return $"SELECT COUNT(1) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #region aggregate

        public override string SqlExists()
        {
            return $"SELECT 1 FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy} LIMIT 1";
        }

        public override string SqlMin(string field)
        {
            return $"SELECT MIN({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlMax(string field)
        {
            return $"SELECT MAX({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlSum(string field)
        {
            return $"SELECT SUM({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlAvg(string field)
        {
            return $"SELECT AVG({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #endregion
    }
}
